package com.engine.camci.utils;


import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * @Title: ecology-idea
 * @Company: 泛微软件
 * @author: weaver.Dusongsong
 * @version: 1.0
 * @CreateDate: 2022-06-16
 * @Description: 导出ExcelUtil
 * @ModifyLog:
 **/

public class ExcelUtil {
    /**
     * 生成Excel 并放到指定位置
     *@param titlelist 标题名称list
     *@param zdlist 字段list
     *@param datalist 数据list
     *@return 是否正常生成
     *@throws IOException
     *@author:
     * (titlelist  和  zdlist  顺序要一致, 要一一对应)
     */
    public static int createTaxInfoExcel(String sheetname, String creater,String approver,List<String> titlelist, List<String> zdlist, List<Map<String,Object>> datalist) throws IOException {
        int docid = -1;
        String filename = sheetname+".xls";
        try {
            //创建HSSFWorkbook对象(excel的文档对象)
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sheet = wb.createSheet(sheetname);
            HSSFCellStyle cellStyle = wb.createCellStyle();
            cellStyle.setAlignment( HorizontalAlignment.CENTER);
            cellStyle.setBorderRight( BorderStyle.THIN);
            cellStyle.setBorderLeft( BorderStyle.THIN);
            cellStyle.setBorderTop( BorderStyle.THIN);
            cellStyle.setBorderBottom( BorderStyle.THIN);
            HSSFRow row0 = sheet.createRow(0);
            sheet.setColumnWidth(0, 100 * 256);
            Cell cell = row0.createCell( 0 );
            cell.setCellValue(sheetname);
            cell.setCellStyle( cellStyle );
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, titlelist.size()-1));
            // 在sheet里创建第一行，参数为行索引(excel的行)，可以是0～65535之间的任何一个
            HSSFRow row1 = sheet.createRow(1);
            // 添加表头
            for(int i = 0;i<titlelist.size();i++){
                 cell = row1.createCell(i);
                 cell.setCellValue(titlelist.get(i));
                 cell.setCellStyle( cellStyle );
            }
            //添加表中内容
            String sum_yfje = "0.00";
            String sum_dkgs = "0.00";
            String sum_sfje = "0.00";
            for(int row = 0;row<datalist.size();row++){//数据行
                HSSFRow newrow = sheet.createRow(row+2);//数据从第三行开始
                Map<String,Object> data = datalist.get(row); //获取该行的数据
                for(int col = 0;col<zdlist.size();col++){//列
                    cell = newrow.createCell(col);
                    cell.setCellStyle( cellStyle );
                    if(data!=null&&data.get(zdlist.get(col))!=null){
                        cell.setCellValue(String.valueOf(data.get(zdlist.get(col))));
                        if(col==3){
                            sum_yfje = CalUtil.fAdd(sum_yfje,(String) data.get(zdlist.get(col)));
                        }else if(col==4){
                            sum_dkgs = CalUtil.fAdd(sum_dkgs,(String) data.get(zdlist.get(col)));
                        }else if(col==5){
                            sum_sfje = CalUtil.fAdd(sum_sfje,(String) data.get(zdlist.get(col)));
                        }
                    }else {
                        cell.setCellValue("");
                    }


                }
            }
            HSSFRow sunrow = sheet.createRow(datalist.size()+2);
            for(int i = 0;i<titlelist.size();i++){
                cell = sunrow.createCell(i);
                if(i<3){
                    cell.setCellValue("合计:");
                }else if(i==3){
                    cell.setCellValue(sum_yfje);
                }else if(i==4){
                    cell.setCellValue(sum_dkgs);
                }else if(i==5){
                    cell.setCellValue(sum_sfje);
                }

                cell.setCellStyle( cellStyle );
            }
            sheet.addMergedRegion(new CellRangeAddress(datalist.size()+2, datalist.size()+2, 0, 2));
            HSSFRow tailrow = sheet.createRow(datalist.size()+4);
            tailrow.createCell(0);
            tailrow.createCell(1).setCellValue("审核:"+approver);
            tailrow.createCell(2);
            sheet.addMergedRegion(new CellRangeAddress(datalist.size()+4, datalist.size()+4, 1, 2));
            tailrow.createCell(3);
            tailrow.createCell(4).setCellValue("制单:"+creater);
            tailrow.createCell(5);
            sheet.addMergedRegion(new CellRangeAddress(datalist.size()+4, datalist.size()+4, 4, 5));
            //输出Excel文件1
            ByteArrayOutputStream os = new ByteArrayOutputStream();
            wb.write(os);
            byte[] content = os.toByteArray();
            docid = CamciUtil.createDoc(filename,content,7);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return docid;
    }

    public static int generateTaxInfoData(String hymc,String creater,String approver,List<Map<String,Object>> datalist) throws Exception{
        int docid = -1;
        String sheetname = hymc+"专家咨询费代扣个税信息表";
        List<String> titlelist = new ArrayList<>();
        titlelist.add("序号");
        titlelist.add("姓名");
        titlelist.add("身份证号");
        titlelist.add("应发金额");
        titlelist.add("代扣个税");
        titlelist.add("实发金额");
        List<String> zdlist = new ArrayList<>();
        zdlist.add( "xh" );
        zdlist.add( "xm" );
        zdlist.add( "sfzh" );
        zdlist.add( "yfje" );
        zdlist.add( "dkgs" );
        zdlist.add( "sfje" );
        docid = createTaxInfoExcel(sheetname,creater,approver,titlelist,zdlist,datalist);
        return docid;
    }

}
